<%@ page import="java.sql.Statement" import="java.sql.ResultSet" import="java.sql.Connection" %> 
<%@page import="connection.DBConnection"%>
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"  pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Supplier Ledger Periodic Report</title>
</head>
<body>

<h2 align="center">Supplier Ledger Periodic Report</h2>
<form name="supplierledgerreport" method="get" >

<%
		String selectedaccname = (String) request.getAttribute("accountname");
		String selectedacccode = (String) request.getAttribute("accountcode");
		String selectedaccgroup = (String) request.getAttribute("accountgroup");
		String dte=(String) request.getAttribute("frmdte");
		 String dte1=(String) request.getAttribute("todte");
		
		DBConnection dbc=new DBConnection();
		Connection con=dbc.getNewConnection();
		Statement st = null;
		ResultSet rs = null;   
		String d="";
		double total=0.0;
		double as14=0.0, as24=0.0, ar14=0.0, ar24=0.0, ap14=0.0, ap24=0.0, aj14=0.0, aj24=0.0,am1=0.0,m=0.0;
		%>
<table align="center" border=5 >
<tr><td colspan=5>From :<%=dte %><br>  To: <%=dte1 %></tr>
<%
		try
   		{
		   st=con.createStatement();
		   rs=st.executeQuery("SELECT code, name, Date, sum(amount), groupname FROM ( (SELECT scode AS code, pvsname AS name, date as Date, -(pvtota) AS amount, pgroupname AS groupname FROM purchasevoucher2) UNION ALL (SELECT dbscode AS code, dbsname AS name, date as Date, dbtotal AS amount, dbgroupname AS groupname FROM DebitNote) UNION ALL (SELECT raccid AS code, raccname AS name, date as Date, -(ramount) AS amount, rgroupname AS groupname FROM receiptsvoucher2) UNION ALL (SELECT racc2id AS code, raccname2 AS name, date as Date, -(ramount2) AS amount, rgroupname2 AS groupname FROM receiptsvoucher2) UNION ALL (SELECT pyaccid AS code, pyaccname AS name, date as Date, pyamount AS amount,pygroupname AS groupname FROM paymentvoucher2) UNION ALL (SELECT pyacc2id AS code, pyaccname2 AS name, date as Date, pyamount2 AS amount, pygroupname2 AS groupname FROM paymentvoucher2) UNION ALL (SELECT jaccid AS code, jaccdeb AS name, date as Date, jamount1 AS amount, jgroupname AS groupname FROM journalvoucher2) UNION ALL (SELECT jacc2id AS code, jacccred AS name, date as Date, -(jamount2) AS amount,jgroupname2 AS groupname FROM journalvoucher2) ) p where (code='"+selectedacccode+"' and groupname='"+selectedaccgroup+"') and  Date < '"+dte+"' ");
		   
		   while(rs.next())
		   {%>
		   
		   <%
   		String c=rs.getString(1);
    		String n=rs.getString(2);
    		d=rs.getString(3);
    		 am1=rs.getDouble(4);
    		 String am2=rs.getString(5);
    		 m=m+am1; 
    		%>
 <% 
		   }
		   
   }
   catch(Exception e)
   {}%>
 
 
 
<tr>
		<th> Date </th>
		<th> Particulars </th>
		<th> Voucher type </th>
 		<th> Voucher Number </th>
 		<th> Amount </th>
</tr>

<tr><td></td><th>Opening Balance</th><td></td><td></td><td><%=m %></td></tr>
		
		
		
   	<%	
		try
   		{
		   st=con.createStatement();
		   rs=st.executeQuery("SELECT pvinno,date,pvsname,pvtota FROM PurchaseVoucher2 where (pvsname='"+selectedaccname+"' AND scode='"+selectedacccode+"') and (date  between '"+dte+"' and '"+dte1+"');");
		   
		   while(rs.next())
		   {
		        String s1=rs.getString(1); //purchase voucher number      
		        String s2=rs.getString(2); //date
		        String s3=rs.getString(3); //account name
				double s4=rs.getDouble(4); //amount
				
				as14=as14+s4;
%>
<tr>
			<td> <%=s2 %> </td>
			<td> Total Purchase </td>
			<td> Purchase </td>
			<td> <a href="http://localhost:8081/BOA_Web/LedgerReportServlet?vouchernumber=<%=s1%>&group=Purchase"> <%=s1 %> </a></td>
			<td> <%=s4 %> </td>	
</tr>
<%		   
		   }
   }
   catch(Exception e)
   {}
%>

<%
		try
   		{
		   st=con.createStatement();
		   rs=st.executeQuery("SELECT renum,date,bname,-(ramount) FROM ReceiptsVoucher2 where (raccname='"+selectedaccname+"' AND raccid='"+selectedacccode+"') and (date between '"+dte+"' and '"+dte1+"');");
		   
		   while(rs.next())
		   {
		        String r11=rs.getString(1); //receipts voucher number	        
		        String r12=rs.getString(2); //date
		        String r13=rs.getString(3); //bank name
				double r14=rs.getDouble(4); //amount
				
				ar14=ar14+r14;
%>
<tr>
			<td> <%=r12 %> </td>
			<td> <%=r13 %> </td>
			<td> Receipts </td>
			<td> <a href="http://localhost:8081/BOA_Web/LedgerReportServlet?vouchernumber=<%=r11%>&group=Receipts"> <%=r11 %> </a></td>
			<td> <%=r14 %> </td>	
</tr>
<%		   
		   }
   }
   catch(Exception e)
   {}
%>

<%
		try
		{
		   st=con.createStatement();
		   rs=st.executeQuery("SELECT renum,date,bname,-(ramount2) FROM ReceiptsVoucher2 where (raccname2='"+selectedaccname+"' AND racc2id='"+selectedacccode+"') and (date between '"+dte+"' and '"+dte1+"');");
		   
		   while(rs.next())
		   {
		        String r21=rs.getString(1); //receipts voucher number	        
		        String r22=rs.getString(2); //date
		        String r23=rs.getString(3); //account name
				double r24=rs.getDouble(4); //amount22
				
				ar24=ar24+r24;
%>
<tr>
			<td> <%=r22 %> </td>
			<td> <%=r23 %> </td>
			<td> Receipts </td>
			<td> <a href="http://localhost:8081/BOA_Web/LedgerReportServlet?vouchernumber=<%=r21%>&group=Receipts"> <%=r21 %> </a></td>
			<td> <%=r24 %> </td>	
</tr>
<%		   
		   }
		}
		catch(Exception e)
		{}
%>


<%
		try
		{
		   st=con.createStatement();
		   rs=st.executeQuery("SELECT pyenum,date,bname,pyamount FROM PaymentVoucher2 where (pyaccname='"+selectedaccname+"' AND pyaccid='"+selectedacccode+"') and (date between '"+dte+"' and '"+dte1+"');");
		   
		   while(rs.next())
		   {
		        String p11=rs.getString(1); //Payment voucher number	        
		        String p12=rs.getString(2); //date
		        String p13=rs.getString(3); //bank name
				double p14=rs.getDouble(4); //amount
				
				ap14=ap14+p14;
%>
<tr>
			<td> <%=p12 %> </td>
			<td> <%=p13 %> </td>
			<td> Payment </td>
			<td> <a href="http://localhost:8081/BOA_Web/LedgerReportServlet?vouchernumber=<%=p11%>&group=Payment"> <%=p11 %> </a></td>
			<td> <%=p14 %> </td>	
</tr>
<%		   
		   }
		}
		catch(Exception e)
		{}
%>

<%
		try
		{
		   st=con.createStatement();
		   rs=st.executeQuery("SELECT pyenum,date,bname,pyamount2 FROM PaymentVoucher2 where (pyaccname2='"+selectedaccname+"' AND pyacc2id='"+selectedacccode+"') and (date between '"+dte+"' and '"+dte1+"');");
		   
		   while(rs.next())
		   {
		        String p21=rs.getString(1); //Payment voucher number	        
		        String p22=rs.getString(2); //date
		        String p23=rs.getString(3); //bank name
				double p24=rs.getDouble(4); //amount
				
				ap24=ap24+p24;
%>
<tr>
			<td> <%=p22 %> </td>
			<td> <%=p23 %> </td>
			<td> Payment </td>
			<td> <a href="http://localhost:8081/BOA_Web/LedgerReportServlet?vouchernumber=<%=p21%>&group=Payment"> <%=p21 %> </a></td>
			<td> <%=p24 %> </td>	
</tr>
<%		   
		   }
		}
		catch(Exception e)
		{}
%>


<%
		try
		{
		   st=con.createStatement();
		   rs=st.executeQuery("SELECT jname,date,jaccdeb,jamount1 FROM JournalVoucher2 where (jaccdeb='"+selectedaccname+"' AND jaccid='"+selectedacccode+"') and (date between '"+dte+"' and '"+dte1+"');");
		   
		   while(rs.next())
		   {
		        String j11=rs.getString(1); //Journal voucher number	        
		        String j12=rs.getString(2); //date
		        String j13=rs.getString(3); //account name
				double j14=rs.getDouble(4); //amount
				
				aj14=aj14+j14;
%>
<tr>
			<td> <%=j12 %> </td>
			<td> <%=j13 %> </td>
			<td> Journal </td>
			<td> <a href="http://localhost:8081/BOA_Web/LedgerReportServlet?vouchernumber=<%=j11%>&group=Journal"> <%=j11 %> </a></td>
			<td> <%=j14 %> </td>	
</tr>
<%		   
		   }
		}
		catch(Exception e)
		{}
%>

<%
		try
		{
		   st=con.createStatement();
		   rs=st.executeQuery("SELECT jname,date,jacccred,-(jamount2) FROM JournalVoucher2 where (jacccred='"+selectedaccname+"' AND jacc2id='"+selectedacccode+"') and (date between '"+dte+"' and '"+dte1+"');");
		   
		   while(rs.next())
		   {
		        String j21=rs.getString(1); //Journal voucher number	        
		        String j22=rs.getString(2); //date
		        String j23=rs.getString(3); //account name
				double j24=rs.getDouble(4); //amount
				
				aj24=aj24+j24;
%>
<tr>
			<td> <%=j22 %> </td>
			<td> <%=j23 %> </td>
			<td> Journal </td>
			<td> <a href="http://localhost:8081/BOA_Web/LedgerReportServlet?vouchernumber=<%=j21%>&group=Journal"> <%=j21 %> </a></td>
			<td> <%=j24 %> </td>	
</tr>
<%		   
		   }
		}
		catch(Exception e)
		{}
%>

	<% total=as14+ar14+ar24+ap14+ap24+aj14+aj24; %>
<tr>
		<td> </td>
		<td> Total </td>
		<td> </td>
		<td> </td>
		<td> <%=total %></td>
</tr>


</table>
<%out.print(d); %>

</form>
</body>
</html>